package savelog;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Title: MySQLiteHelper class for CSIS 343 Final Project Description: CS 343
 * Final Project - MySQLiteHelper This class will handle the creation of the SQLite database
 * and prepoulating the Systems table
 * 
 * @author Erik Olson
 * @email erik.olson-5@my.uwrf.edu
 * @author Mike Harmon
 * @email michael.harmon@my.uwrf.edu
 * @author Jack Friermuth
 * @email jack.friermuth@my.uwrf.edu
 * @author Luke Vang
 * @email luke.vang@my.uwrf.edu
 * @author Casey Golla
 * @email casey.golla@my.uwrf.edu
 * @date December 4th 2012
 */
public class MySQLiteHelper extends SQLiteOpenHelper {
	
	//name of our local database
	private static final String DATABASE_NAME = "savelog.db";
	
	//version of our local database
	private static final int DATABASE_VERSION = 1;
	
	 private static MySQLiteHelper instance = null;
	
	//sql strings that will create all of the tables that we need
	private static final String CREATE_SAVES = "				CREATE TABLE Saves ("
													+"			save_id					INTEGER PRIMARY KEY AUTOINCREMENT,"
													+"			game_id					INTEGER,"
													+"			save_descrip	 		TEXT,"
													+"			save_date_created 		TEXT,"
													+"			save_date_logged		TEXT,"
													+"			save_src				TEXT,"
													+"			dev_id					INTEGER NOT NULL,"
													+"			foreign key (dev_id) references Devices(id));";
	
	private static final String CREATE_DEVICES =  "			CREATE TABLE Devices ("
													+"			dev_id					INTEGER PRIMARY KEY AUTOINCREMENT,"
													+"			dev_name				TEXT);";
	
	private static final String CREATE_GAMES =   "			CREATE TABLE Games ("
													+"			game_id					INTEGER PRIMARY KEY AUTOINCREMENT,"
													+"			game_title				TEXT,"
													+"			game_cover	 			TEXT,"
													+"			dev_id					INTEGER NOT NULL,"
													+"			sys_id					INTEGER NOT NULL,"
													+"			foreign key (sys_id) references Systems(id),"
													+"			foreign key (dev_id) references Devices(id));";
	
	private static final String CREATE_SYSTEMS =  "			CREATE TABLE Systems ("
													+"			sys_id					INTEGER PRIMARY KEY AUTOINCREMENT,"
													+"			sys_name				TEXT,"
													+"			sys_directory 			TEXT,"
													+"			sys_ext					TEXT,"
													+"			sys_is_active			INTEGER DEFAULT 0,"
													+"			theme_id				INTEGER,"
													+"			foreign key (theme_id) references Themes(id));";
	
	private static final String CREATE_THEMES =  "			CREATE TABLE Themes ("
													+"			theme_id				INTEGER PRIMARY KEY AUTOINCREMENT,"
													+"			theme_text_primary		TEXT,"
													+"			theme_text_secondary 	TEXT,"
													+"			theme_background_main	TEXT,"
													+"			theme_background_table	TEXT);";
	
	//sql strings to populate systems 
	private static final String POPULATE_SUPER_NINTENDO = "			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'SNES', 'No Directory Set', 'srm', 0 , 0);";
	
	private static final String POPULATE_N64 = 			"			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'N64', 'No Directory Set', 'srm', 0 , 0);";
	
	private static final String POPULATE_PS1 = 			"			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'PS1', 'No Directory Set', 'PS1?', 0 , 0);";
	
	private static final String POPULATE_GENESIS = 		"			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'SEGA', 'No Directory Set', 'srm', 0 , 0);";
	
	private static final String POPULATE_GAMEBOY = 		"			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'GBA', 'No Directory Set', 'SAV', 0 , 0);";
	
	private static final String POPULATE_NINTENDO = "			INSERT INTO" +
															"				Systems(sys_name,sys_directory,sys_ext,sys_is_active,theme_id)" +
															"			VALUES(" +
															"				'Nintendo', 'No Directory Set', 'ns', 0 , 0);";
	
	/**
	 * Constructor for MySQLiteHelper that will set up the database
	 * @param Context context :		current state of the application
	 */
	private MySQLiteHelper(Context context) {
	    super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	
	/**
	 * Method to get or create an instance of MySQLiteHelper
	 * @param Context context 			:		current state of the application
	 * @return MySQLiteHelper instance :	An instance of MySQLiteHelper
	 */
	public static synchronized MySQLiteHelper getInstance( Context context ) {
        if (instance == null) {
                instance = new MySQLiteHelper( context );
        }
        return instance;
	}

	/**
	 * Method that will fire every time a MySQLiteHelper object is created. This method will load all of the tables into the database.
	 * @Override
	 * @param SQLiteDatabase db : an sqlite database object that you want to populate
	 */
	public void onCreate(SQLiteDatabase db) {
		 db.execSQL(CREATE_SAVES);
		 db.execSQL(CREATE_DEVICES);
		 db.execSQL(CREATE_GAMES);
		 db.execSQL(CREATE_SYSTEMS);
		 db.execSQL(CREATE_THEMES);
		 
		 db.execSQL(POPULATE_SUPER_NINTENDO);
		 db.execSQL(POPULATE_N64);
		 db.execSQL(POPULATE_PS1);
		 db.execSQL(POPULATE_GENESIS);
		 db.execSQL(POPULATE_GAMEBOY);
		 db.execSQL(POPULATE_NINTENDO);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

}
